using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;
using System.Xml;

namespace Cache
{
    public partial class frmImport : Form
    {
        public string rt = null;
        public string Lat = null;
        public string Lon = null;
        public string CName = null;
        public string Desc = null;
        public string URL = null;
        public string URLName = null;
        public string Sym = null;
        public string PlacedBy = null;
        public string COwner = null;
        public string Type = null;
        public string CContainer = null;
        public string Difficulty = null;
        public string Terrain = null;
        public string Country = null;
        public string state = null;
        public string ShortDesc = null;
        public string LongDesc = null;
        public string EncHints = null;
        public string Available = null;
        public string Archived = null;
        static XmlDocument doc = new XmlDocument();

        public int TotalCaches = 0;
        public int UpdatedCaches = 0;
        public int NewCaches = 0;

        public int WorkToDo = 0;
        public string LogID = null;
        public string LogDate = null;
        public string LogType = null;
        public string Finder = null;
        public string LogText = null;

        public frmImport()
        {
            InitializeComponent();
        }

        private void cmdOpenFile_Click(object sender, EventArgs e)
        {
            OpenFileDialog OFD1 = new OpenFileDialog();
            OFD1.Title = "Select GPX File...";
            OFD1.InitialDirectory = Application.StartupPath.ToString();
            OFD1.Filter = "GPX Files (*.gpx)|*.gpx";
            OFD1.RestoreDirectory = true;

            if (OFD1.ShowDialog() == DialogResult.OK)
            {
                Cursor.Current = Cursors.WaitCursor;
                txtFileName.Text = OFD1.FileName.ToString();
                Cursor.Current = Cursors.Arrow;
            }

        }

        private void frmImport_Load(object sender, EventArgs e)
        {
            pbImportProgress.Enabled = false;

        }

        private void cmdImport_Click(object sender, EventArgs e)
        {
            pbImportProgress.Enabled = true;
            pbImportProgress.Value = 0;
            txtFileName.Enabled = false;
            cmdImport.Enabled = false;
            cmdOpenFile.Enabled = false;
            txtFileName.ReadOnly = true;
            grpOptions.Enabled = false;
            OpenGPXFile(txtFileName.Text);
            txtFileName.Enabled = true;
            cmdImport.Enabled = true;
            cmdOpenFile.Enabled = true;
            txtFileName.ReadOnly = false;
            grpOptions.Enabled = true;

        }


        private void AddCache()
        {
            //SQLiteBase db = new SQLiteBase(Application.StartupPath + @"Geocaching.db");
            SQLiteConnection conn = new SQLiteConnection("data source=" + Application.StartupPath + @"\GeoCaches.db");
            SQLiteDataReader rdr = null;

            conn.Open();

            //MessageBox.Show("Add Cache to DB");
            Sanitize();

            string mySQL;

            mySQL = "INSERT INTO tblGeoCache (";
            mySQL += "Name, Desc, URL, URLName, Sym, PlacedBy, Owner, Type, Container, Difficulty, Terrain, ";
            mySQL += "Country, State, ShortDescription, LongDescription, EncodedHints, Latitude, Longitude, ";
            mySQL += "Available, Archived, Status) ";
            mySQL += "VALUES ('" + CName + "', '" + Desc + "', '" + URL + "', '" + URLName + "', '" + Sym + "', '";
            mySQL += PlacedBy + "', '" + COwner + "', '" + Type + "', '" + CContainer + "', '" + Difficulty + "', '";
            mySQL += Terrain + "', '" + Country + "', '" + state + "', '" + ShortDesc + "', '";
            mySQL += LongDesc + "', '" + EncHints + "', '" + Lat + "', '" + Lon + "', '" + Available + "', '";
            mySQL += Archived + "', 'NotFound')";

            SQLiteCommand cmd = new SQLiteCommand(mySQL, conn);

            try
            {
                rdr = cmd.ExecuteReader();
                NewCaches += 1;
                pbImportProgress.Value += 1;
                rdr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString() + "\r\n\r\nSQL: " + mySQL);
            }
        }

        private void Sanitize()
        {

            CName = CName.Replace("'", "''");
            Desc = Desc.Replace("'", "''");
            URL = URL.Replace("'", "''");
            URLName = URLName.Replace("'", "''");
            Sym = Sym.Replace("'", "''");
            PlacedBy = PlacedBy.Replace("'", "''");
            COwner = COwner.Replace("'", "''");
            Type = Type.Replace("'", "''");
            CContainer = CContainer.Replace("'", "''");
            Difficulty = Difficulty.Replace("'", "''");
            Terrain = Terrain.Replace("'", "''");
            Country = Country.Replace("'", "''");
            state = state.Replace("'", "''");
            ShortDesc = ShortDesc.Replace("'", "''");
            LongDesc = LongDesc.Replace("'", "''");
            EncHints = EncHints.Replace("'", "''");

        }

        private void CheckDupe()
        {
            SQLiteConnection conn = new SQLiteConnection("data source=" + Application.StartupPath + @"\GeoCaches.db");

            SQLiteDataReader rdr = null;
            SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM tblGeoCache WHERE Name = '" + CName + "'", conn);
            //SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM tblGeoCache WHERE Name = 'GCGTW3'", conn);            

            conn.Open();
            try
            {
                rdr = cmd.ExecuteReader();

                //MessageBox.Show("Name: |" + Name + "|\r\nHas Rows: " +rdr.HasRows.ToString());

                /*
                while (rdr.Read())
                {
                    MessageBox.Show("Name from DB: " + rdr["Name"].ToString() + "\r\nName from Var: " + Name);
                }
                */
                //MessageBox.Show(rdr.HasRows.ToString());
                if (rdr.HasRows == true)
                {
                    //If the name exists then run an update in the database.
                    //MessageBox.Show("Update Record");

                    //Need to close the reader and the connection to the DB otherwise the file is locked.
                    rdr.Close();
                    conn.Close();

                    //Update the record.
                    MessageBox.Show(CName);
                    UpdateExisting();
                    
                }
                else
                {
                    //If no rows exist then we will add the record for the first time.
                    
                    AddCache();
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

            rdr.Close();
            conn.Close();

        }

        private void UpdateExisting()
        {
            SQLiteConnection conn = new SQLiteConnection("data source=" + Application.StartupPath + @"\GeoCaches.db");

            conn.Open();
            string mySQL;

            Sanitize();

            mySQL = "UPDATE tblGeoCache SET ";
            mySQL += "Name = '" + CName + "', Desc = '" + Desc + "', URL = '" + URL + "', ";
            mySQL += "URLName = '" + URLName + "', Sym = '" + Sym + "', PlacedBy = '" + PlacedBy + "', ";
            mySQL += "Owner = '" + COwner + "', Type = '" + Type + "', Container = '" + CContainer + "', ";
            mySQL += "Difficulty = '" + Difficulty + "', Terrain = '" + Terrain + "', Country = '" + Country + "', ";
            mySQL += "State = '" + state + "', ShortDescription = '" + ShortDesc + "', LongDescription = '" + LongDesc + "', ";
            mySQL += "EncodedHints = '" + EncHints + "', Latitude = '" + Lat + "', Longitude = '" + Lon + "', ";
            mySQL += "Available = '" + Available + "', Archived = '" + Archived + "' ";
            mySQL += "WHERE Name = '" + CName + "'";


            SQLiteCommand cmd = new SQLiteCommand(mySQL, conn);

            try
            {
                cmd.ExecuteNonQuery();
                //MessageBox.Show("Update Complete");
                UpdatedCaches += 1;
                pbImportProgress.Value += 1;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString() + "\r\n\r\nSQL Statement: " + mySQL);
            }
        } //END UpdateExisting()

        private void OpenGPXFile(string FileName)
        {
            doc.Load(FileName);
            XmlNodeList wptList = doc.GetElementsByTagName("wpt");
            TotalCaches = Convert.ToInt32(wptList.Count.ToString());
            pbImportProgress.Maximum = TotalCaches;
            ReadGPX(); //Read the GPX file.
        }

        private void ReadGPX()
        {

            int wptCount = 0;
            XmlNodeList wptList = doc.GetElementsByTagName("wpt");
            //TotalCaches = Convert.ToInt32(wptList.Count.ToString());



            foreach (XmlNode node in wptList)
            {

                XmlElement wptElement = (XmlElement)node;

                //MessageBox.Show(cnt.ToString());

                if (wptElement.HasAttributes)
                {
                    Lat = wptElement.Attributes["lat"].InnerText;
                    Lon = wptElement.Attributes["lon"].InnerText;
                }

                if (wptElement.HasChildNodes)
                {

                    foreach (XmlNode cnode in wptElement.ChildNodes)
                    {

                        switch (cnode.Name.ToString())
                        {
                            case "name":
                                CName = cnode.InnerText.ToString();
                                break;

                            case "desc":
                                Desc = cnode.InnerText.ToString();
                                break;

                            case "url":
                                URL = cnode.InnerText.ToString();
                                break;

                            case "urlname":
                                URLName = cnode.InnerText.ToString();
                                break;

                            case "sym":
                                Sym = cnode.InnerText.ToString();
                                break;

                            case "type":
                                Type = cnode.InnerText.ToString();
                                break;

                            case "groundspeak:cache":
                                ReadGSInfo(wptCount);
                                wptCount += 1;
                                break;


                        }//End Switch

                    }//End ForEach(XmlNode node in wptList)

                }//End If


            }
            MessageBox.Show("DONE! \r\nTotal Caches: " + TotalCaches + "\r\nUpdated Caches: " + UpdatedCaches + "\r\nNew Caches: " + NewCaches);

        }//END: ReadGPX()

        private void ReadGSInfo(int wptCount)
        {
            int GSCnt = 0;
            //Create a list of the nodes below Groundspeak:cache.
            XmlNodeList GroundSpeakList = doc.GetElementsByTagName("groundspeak:cache");


            //MessageBox.Show(GroundSpeakList.Count.ToString());

            //list all of the child nodes below groundspeak:cache.

            XmlElement GSElement = (XmlElement)GroundSpeakList[wptCount];
            //MessageBox.Show("Child Nodes: " + GSElement.ChildNodes.Count.ToString() + "\r\n" + GSElement.InnerText.ToString());

            //Get Available and Archived Attributes if they exist.
            if (GSElement.HasAttributes)
            {
                Available = GSElement.Attributes["available"].InnerText;
                Archived = GSElement.Attributes["archived"].InnerText;
            }

            foreach (XmlNode GScnode in GSElement.ChildNodes)
            {
                //MessageBox.Show(GScnode.Name.ToString());
                //Assign the data from the GroundSpeak data to variables.
                switch (GScnode.Name.ToString())
                {
                    case "groundspeak:placed_by":
                        PlacedBy = GScnode.InnerText;
                        break;

                    case "groundspeak:owner":
                        COwner = GScnode.InnerText;
                        break;

                    case "groundspeak:container":
                        CContainer = GScnode.InnerText;
                        break;

                    case "groundspeak:difficulty":
                        Difficulty = GScnode.InnerText;
                        break;

                    case "groundspeak:terrain":
                        Terrain = GScnode.InnerText;
                        break;

                    case "groundspeak:country":
                        Country = GScnode.InnerText;
                        break;

                    case "groundspeak:state":
                        state = GScnode.InnerText;
                        break;

                    case "groundspeak:short_description":
                        ShortDesc = GScnode.InnerText;
                        break;

                    case "groundspeak:long_description":
                        LongDesc = GScnode.InnerText;
                        break;

                    case "groundspeak:encoded_hints":
                        EncHints = GScnode.InnerText;
                        //CheckDupe();
                        //SaveData();
                        break;

                    case "groundspeak:logs":
                        //ReadGSLogs(GSCnt);
                        GSCnt += 1;
                        break;
                }  //End Switch
            } //End ForEach

            //All Waypoint data has been read and stored into the variables.
            //DisplayWPT(); //This is for testing purposes only.
            CheckDupe();
        } //End ReadGSInfo()


    }
}